package com.myres.firstmenu.db;

import com.myres.db.ConnectionPool;
import com.myres.model.Category;
import com.myres.model.Serial;

import java.util.List;
import java.util.ArrayList;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class CategoryService {
	 public Category getCategory(int id){
		 Connection con=null;
    	 PreparedStatement pstmt=null;
    	 ResultSet rs=null;
    	 try{
    		 con=ConnectionPool.getPool().getConnection();
    		 pstmt=con.prepareStatement("select * from res_categories where _id= ? order by _priority asc");
    		 pstmt.setInt(1,id);
    		 rs=pstmt.executeQuery();
    		 return this.populate(rs).get(0);
    	 }catch(Exception e){
    		 e.printStackTrace();
    		 return null;
    	 }finally{
    		 try{
    		     rs.close();
    		     pstmt.close();
    		     con.close();
    		 }catch(Exception e){
    		 }
    		 
    	 }
	 }
	 public int insertCategory(String name,String summary,int priority){
		 Connection con=null;
    	 PreparedStatement pstmt=null;
    	 try{
    		 con=ConnectionPool.getPool().getConnection();
    		 pstmt=con.prepareStatement("insert into res_categories (_name,_summary,_priority) value(?,?,?)");
    		 pstmt.setString(1,name);
    		 pstmt.setString(2, summary);
    		 pstmt.setInt(3, priority);
    		 return pstmt.executeUpdate();
    	 }catch(Exception e){
    		 e.printStackTrace();
    		 return -1;
    	 }finally{
    		 try{
    		     pstmt.close();
    		     con.close();
    		 }catch(Exception e){
    		 }
    		 
    	 }
	 }
	 
	 public int insertSerial(String name,int cid){
		 Connection con=null;
    	 PreparedStatement pstmt=null;
    	 try{
    		 con=ConnectionPool.getPool().getConnection();
    		 pstmt=con.prepareStatement("insert into res_series (_name,_cid) value(?,?)");
    		 pstmt.setString(1,name);
    		 pstmt.setInt(2, cid);
    		 return pstmt.executeUpdate();
    	 }catch(Exception e){
    		 e.printStackTrace();
    		 return -1;
    	 }finally{
    		 try{
    		     pstmt.close();
    		     con.close();
    		 }catch(Exception e){
    		 }
    		 
    	 }
	 }
	 public List<Category> getJustInsertCategrory(String name){
		 Connection con=null;
    	 PreparedStatement pstmt=null;
    	 ResultSet rs=null;
    	 try{
    		 con=ConnectionPool.getPool().getConnection();
    		 pstmt=con.prepareStatement("select * from res_categories where _name = ? order by _id desc");
    		 pstmt.setString(1, name);
    		 rs=pstmt.executeQuery();
    		 return this.populate(rs);
    	 }catch(Exception e){
    		 e.printStackTrace();
    		 return null;
    	 }finally{
    		 try{
    		     rs.close();
    		     pstmt.close();
    		     con.close();
    		 }catch(Exception e){
    		 }
    		 
    	 }
	 }
	 public List<Serial> getJustInsertSerial(String name){
		 Connection con=null;
    	 PreparedStatement pstmt=null;
    	 ResultSet rs=null;
    	 try{
    		 con=ConnectionPool.getPool().getConnection();
    		 pstmt=con.prepareStatement("select * from res_series where _name = ? order by _id desc");
    		 pstmt.setString(1, name);
    		 rs=pstmt.executeQuery();
    		 return this.populateSerial(rs);
    	 }catch(Exception e){
    		 e.printStackTrace();
    		 return null;
    	 }finally{
    		 try{
    		     rs.close();
    		     pstmt.close();
    		     con.close();
    		 }catch(Exception e){
    		 }
    		 
    	 }
	 }
	 public List<Serial> getSerials(int categoryId){
		 Connection con=null;
    	 PreparedStatement pstmt=null;
    	 ResultSet rs=null;
    	 try{
    		 con=ConnectionPool.getPool().getConnection();
    		 pstmt=con.prepareStatement("select * from res_series where _cid=?");
    		 pstmt.setInt(1, categoryId);
    		 rs=pstmt.executeQuery();
    		 return this.populateSerial(rs);
    	 }catch(Exception e){
    		 e.printStackTrace();
    		 return null;
    	 }finally{
    		 try{
    		     rs.close();
    		     pstmt.close();
    		     con.close();
    		 }catch(Exception e){
    		 }
    		 
    	 }
	 }
     public List<Category> getCategories(){
    	 Connection con=null;
    	 PreparedStatement pstmt=null;
    	 ResultSet rs=null;
    	 try{
    		 con=ConnectionPool.getPool().getConnection();
    		 pstmt=con.prepareStatement("select * from res_categories order by _priority asc");
    		 rs=pstmt.executeQuery();
    		 return this.populate(rs);
    	 }catch(Exception e){
    		 e.printStackTrace();
    		 return null;
    	 }finally{
    		 try{
    		     rs.close();
    		     pstmt.close();
    		     con.close();
    		 }catch(Exception e){
    		 }
    		 
    	 }
     }
     public List<Category> getCategories(int shopId){
    	 Connection con=null;
    	 PreparedStatement pstmt=null;
    	 ResultSet rs=null;
    	 try{
    		 con=ConnectionPool.getPool().getConnection();
    		 pstmt=con.prepareStatement("select * from res_categories where res_categories._id in(" 
    				 +"select res_series._cid from res_series where res_series._id in ("
    				 +"select res_products._sid from res_products where _shop_id =?)" +
    				 ")"
    				 +"order by _priority asc");
    		 pstmt.setInt(1,shopId);
    		 rs=pstmt.executeQuery();
    		 return this.populate(rs);
    	 }catch(Exception e){
    		 e.printStackTrace();
    		 return null;
    	 }finally{
    		 try{
    		     rs.close();
    		     pstmt.close();
    		     con.close();
    		 }catch(Exception e){
    		 }
    		 
    	 }
     }
     private List<Category> populate(ResultSet rs) throws SQLException{
    	 List<Category> l=new ArrayList<Category>();
    	 while(rs.next()){
    		 Category c=new Category();
    		 c.setId(rs.getInt(1));
    		 c.setNO(rs.getString(2));
    		 c.setName(rs.getString(3));
    		 c.setIcon(rs.getString(5));
    		 c.setSummary(rs.getString(6));
             l.add(c);
    	 }
    	 return l;
     }
     private List<Serial> populateSerial(ResultSet rs) throws SQLException{
    	 List<Serial> l=new ArrayList<Serial>();
    	 while(rs.next()){
    		 Serial c=new Serial();
    		 c.setId(rs.getInt(1));
    		 c.setNO(rs.getString(2));
    		 c.setCid(rs.getInt(3));
    		 c.setName(rs.getString(4));
             l.add(c);
    	 }
    	 return l;
     }
}
